<#import "../main.html" as page>
<@page.main current="/dbDoc" title="数据库文档">
<style type="text/css">
    .divTableList {
        width: 180px;
        height: 30px;
        line-height: 30px;
        margin: 10px 30px 0 30px;
        background: #dddddd;
        display: inline-block;
        transition: 0.2s;
        text-align: center;
        cursor: pointer;
        word-break: break-all;
        word-wrap: break-word;
    }

    .divTableList:hover {
        background: #dddddd;
        box-shadow: 0 0 5px blue;
    }

    .divContent {
        box-shadow: 0 0 10px grey;
        padding: 20px;
    }

    .dbName {
        font-weight: bold;
        margin: 20px 0 10px 5px;
    }

    .divContent .tbDatas {
        width: 100%;
    }

    .tbDatas thead {
        font-weight: bold;
        background-color: darkgray;
    }

    .tbDatas tbody {
        background-color: lightgray;
    }

    .tbDatas tbody tr:hover {
        background-color: darkgray;
    }

    .tbDatas td {
        padding: 5px;
        white-space: nowrap;
    }

    .btnGetData {
        width: 18px;
        height: 18px;
        position: relative;
        top: 20px;
        float: right;
    }

    #mask {
        position: fixed;
        padding: 50px;
        top: 0;
        left: 0;
        width: 100%;
        height: 100%;
        z-index: 999;
        background: white;
        transition: 0.3s;
    }

    /* basic style */
    .close {
        /* still bad on picking color */
        background: white;
        color: grey;
        /* make a round button */
        border-radius: 12px;
        /* center text */
        line-height: 20px;
        text-align: center;
        height: 20px;
        width: 20px;
        font-size: 18px;
        padding: 1px;
        cursor: pointer;
    }

    /* use cross as close button */
    .close::before {
        content: "\2716";
    }

    /* place the button on top-right */
    .close {
        top: 15px;
        right: 115px;
        position: absolute;
    }

    .divActTitle {
        width: 100%;
        font-weight: bold;
        text-align: center;
        font-size: 30px;
        margin-bottom: 30px;
    }

    .divTableDetailContent {
        height: 80%;
        overflow-y: auto;
        padding-bottom: 30px;
    }

    .txtLight {
        color: lightgrey;
    }

    .ulHistory {
        max-width: 675px;
        width: 100%;
        margin: 5px 0;
        padding: 5px 10px 5px 15px;
        background-color: #eeeeee;
    }

    .ulHistory li {
        list-style: none;
        margin: 5px 0;
    }

    .ulHistory li label {
        cursor: pointer;
        transition: 0.2s;
    }

    .ulHistory li span {
        top: 4px;
    }

    .ulHistory li label:hover {
        font-weight: bolder;
    }

</style>

<div id="pageMSSQL" class="divPage">
    <div class="divTitleText"></div>
    <div class="divAct">
        <div>
            <button id="btnSearch" class="jk-button mr10">查询</button>
        </div>
        <table style="margin-top: 10px;width:100%">
            <tr>
                <td style="width: 90px">数据库类型:</td>
                <td>
                    <input type="radio" name="dbType" id="cboMySql" value="MySql" checked="checked"/>
                    <label for="cboMySql">MySql</label>
                    <input type="radio" name="dbType" id="cboPostgreSql" value="PostgreSql"/>
                    <label for="cboPostgreSql">PostgreSql</label>
                    <input type="radio" name="dbType" id="cboSqlServer" value="SqlServer"/>
                    <label for="cboSqlServer">Sql Server</label>
                </td>
            </tr>
            <tr>
                <td>连接字符串:</td>
                <td>
                    <input type="text" id="txtConnectionString" style="max-width: 686px;width: 100%"
                           value="jdbc:mysql://xxxxxxxxxxxx:xxxx/xxxxxxxxxx"/>
                    <span class="ui-icon ui-icon-circle-triangle-s dn cp" style="top: 1px" id="btnHistory"/>
                </td>
            </tr>
            <tr id="trHistory" class="dn">
                <td></td>
                <td id="tdHistory"></td>
            </tr>
            <tr>
                <td>用户名:</td>
                <td>
                    <input type="text" id="txtUsername" style="max-width: 686px;width: 100%"
                           value="xxxxxxxxxx"/>
                </td>
            </tr>
            <tr>
                <td>密码:</td>
                <td>
                    <input type="text" id="txtPassword" style="max-width: 686px;width: 100%"
                           value="xxxxxxxxxx"/>
                </td>
            </tr>
            <tr class="eleHide dn">
                <td>筛选:</td>
                <td>
                    <input type="text" id="txtDbName" style="max-width: 686px;width: 100%" placeholder="请输入表名"/>
                </td>
            </tr>
        </table>
    </div>
    <div id="divDatas" class="divContent eleHide dn mt15" style="overflow-x:auto">
    </div>
    <div id="divTableNameListTemp" class="dn">
        <div class="divTableList rd5 eleDb" db="{3}" onclick="findTable('{1}');" title="{2}">{0}</div>
    </div>
    <div id="divTableTemp" class="dn">
        <div class="dbName dib"></div>
        <img src="/static/images/search.png" title="查询数据" class="btnGetData cp mr5">
        <table class="tbTemp tbDatas">
            <thead>
            <tr>
                <td>名称</td>
                <td>类型</td>
                <td>长度</td>
                <td>是否为Null</td>
                <td>默认值</td>
                <td>是否为主键</td>
                <td>是否自增</td>
                <td>说明</td>
            </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>
    <form method="post" id="fmExport" style="display: none" action="/tool/Export2File">
        <input name="sql"/>
        <input name="Connection"/>
        <input name="fileName"/>
        <input name="DbType"/>
        <input name="excelType"/>
    </form>

    <div id="mask" class="dn">
        <div class="divActTitle"><label id="lblActTitle"></label></div>
        <div id="btnClose" class="close"
             onclick="$('.divActTitle').fadeOut(100);$('#mask').css('width','0%');$('#mask').fadeOut(200)"></div>
        <div class="divTableDetailContent">
            <div class="">
                <table>
                    <tr>
                        <td>执行方式</td>
                        <td colspan="3">
                            <div id="cboCondition">
                                <label for="radioSearch">查询</label>
                                <input type="radio" name="cboActType" id="radioSearch" checked value="s"/>
                                <label for="radioAdd">新增</label>
                                <input type="radio" name="cboActType" id="radioAdd" value="a"/>
                                <label for="radioEdit">编辑</label>
                                <input type="radio" name="cboActType" id="radioEdit" value="e"/>
                            </div>
                        </td>
                        <td>
                            <button id="btnGo" class="jk-button">GO</button>
                        </td>
                    </tr>
                    <tr class="actSearchShow">
                        <td style="width:100px">页数</td>
                        <td><input type="text" value="1" id="txtPageNum" style="width:100px"/></td>
                        <td style="width:100px">查询条数</td>
                        <td><input type="text" value="10" id="txtPageSize" style="width:100px"/></td>
                    </tr>
                    <tr>
                        <td>字段</td>
                        <td colspan="4">
                            <div id="divCondition"></div>
                        </td>
                    </tr>
                </table>
                <div class="divQueryResult dn actSearchShow" style="width:90%;overflow-x: auto">
                    <table class="tbDatas" style="min-width: 100%">
                        <thead></thead>
                        <tbody></tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<script type="text/javascript">

    $(function () {
        const connId = localStorage.getItem("connId");
        if (connId && connId.length != 0) {
            chooseHistory(connId);
            $("#btnHistory").show();
        } else {
            $("#btnHistory").hide();
        }
        initTrHistory();

        $("input[name=cboActType]").checkboxradio({
            icon: false
        });
        $("#cboCondition").controlgroup();
    });

    function removeLi(id) {
        $("#li" + id).remove();
        removeHistory(id);
        if ($(".ulHistory li").length == 0) {
            $("#btnHistory,#tdHistory").hide();
        }
    }

    function getHistory(id) {
        let connHistoryStr = localStorage.getItem("connHistory");
        if (connHistoryStr) {
            let arr = JSON.parse(connHistoryStr);
            if (!arr || arr.length == 0) {
                return null;
            }
            arr = arr.filter(i => i.id == id)
            return arr != null && arr.length > 0 ? arr[0] : null;
        }
    }

    function initTrHistory() {
        let connHistoryStr = localStorage.getItem("connHistory");
        if (connHistoryStr) {
            let arr = JSON.parse(connHistoryStr);
            if (!arr || arr.length == 0) {
                $("#tdHistory").html("");
                return false;
            }
            let html = "<ul class='ulHistory rd5'>";
            for (let i = 0; i < arr.length; i++) {
                html += "<li id='li" + arr[i].id + "'><label onclick='chooseHistory(\"" + arr[i].id + "\")'>" + (arr[i].conn.length > 90 ? arr[i].conn.substring(0, 90) + "..." : arr[i].conn) + "</label><span class='ui-icon ui-icon-close fr cp' onclick='removeLi(\"" + arr[i].id + "\")'></span></li>";
            }
            $("#tdHistory").html(html + "</ul>");
            $(".ui-icon-close").hover(function () {
                $(this).parent().find("label").css("text-decoration", "line-through");
            }, function () {
                $(this).parent().find("label").css("text-decoration", "");
            });
            return true;
        } else {
            $("#tdHistory").html("");
            return false;
        }
    }

    function chooseHistory(id) {
        let item = getHistory(id);
        $("#txtConnectionString").val(item.conn);
        $("#txtUsername").val(item.user);
        $("#txtPassword").val(item.pwd);
        $("input[value=" + item.dbType + "]").prop("checked", "checked");
        $("#trHistory").hide();
    }

    function addHistory(conn, user, pwd, dbType) {
        const id = hex_md5(conn + user + pwd + dbType);
        removeHistory(id);
        let connHistoryStr = localStorage.getItem("connHistory");
        let connItem = {id, conn, user, pwd, dbType};
        let json;
        if (connHistoryStr) {
            json = JSON.parse(connHistoryStr);
            json.push(connItem);
        } else {
            json = [connItem];
        }
        localStorage.setItem("connHistory", JSON.stringify(json));
        localStorage.setItem("connId", id);
        $("#btnHistory").show();
        initTrHistory();
    }

    function removeHistory(id) {
        let connHistoryStr = localStorage.getItem("connHistory");
        if (connHistoryStr) {
            let arr = JSON.parse(connHistoryStr);
            if (!arr || arr.length == 0) {
                return;
            }
            arr = arr.filter(i => i.id !== id);
            localStorage.setItem("connHistory", JSON.stringify(arr));
            let connId = localStorage.getItem("connId");
            if (connId && connId == id) {
                localStorage.removeItem("connId");
            }
        }
    }

    function findTable(id) {
        //location.href = '#' + id;
        $('html, body').animate({
            scrollTop: $("#" + id).offset().top - 80
        }, 500);
    }

    $("#btnHistory").click(function () {
        $("#trHistory").toggle();
    });

    $("#txtDbName").bind('keyup', function () {
        let text = $(this).val();
        if (text) {
            $(".eleDb").each(function () {
                if ($(this).attr("db").indexOf(text) > -1) {
                    $(this).show();
                } else {
                    $(this).hide();
                }
            });
        } else {
            $(".eleDb").show();
        }
    });

    $("#btnSearch").bind("click", function () {
        const connectionString = $("#txtConnectionString").val().trim();
        if (!connectionString) {
            return;
        }
        const username = $("#txtUsername").val().trim();
        if (!username) {
            return;
        }
        const password = $("#txtPassword").val().trim();
        if (!password) {
            return;
        }
        const dbType = $("input[name=dbType]:radio[checked=checked]").val();

        $("#divDatas").html('');
        $("#txtDbName").val('');
        $(".eleHide,#trHistory").hide();
        $.ajax({
            async: true,
            type: "POST",
            url: "/db/getDbFile",
            cache: false,
            timeout: 60 * 60 * 1000,
            dataType: "json",
            data: {
                connection: encodeURIComponent(connectionString),
                username: encodeURIComponent(username),
                password: encodeURIComponent(password),
                dbType: dbType,
                tableName: null
            },
            success: function (result) {
                if (result != null && result.code != '00000') {
                    alert(result.msg);
                    $(".eleHide").hide();
                    return;
                }
                addHistory(connectionString, username, password, dbType);
                $(".eleHide").fadeIn(200);
                $("#divDatas").scrollLeft(0);

                let divTableListHtml = "";
                let divTableFilesHtml = "";
                for (let i = 0; i < result.data.length; i++) {
                    const row = result.data[i];
                    const idStr = "divTableName_" + row.dbName;
                    const showName = row.dbName.length > 25 ? row.dbName.substring(0, 22) + "..." : row.dbName;
                    const tableNameTitle = row.dbName + (row.comment == '' ? '' : ' (' + row.comment + ")");
                    $("#divTableTemp").find(".dbName").attr("id", idStr).text(tableNameTitle);
                    const tbListHtml = $("#divTableNameListTemp").html();
                    divTableListHtml +=
                        tbListHtml
                            .replace("{0}", showName)
                            .replace("{1}", idStr)
                            .replace("{2}", tableNameTitle)
                            .replace("{3}", row.dbName);

                    let tdHtml = '';
                    for (let j = 0; j < row.fieldDescriptions.length; j++) {
                        const field = row.fieldDescriptions;
                        tdHtml += "<tr><td class='dbColumn'>" + field[j].name + "</td>"
                            + "<td>" + field[j].dbType + "</td>"
                            + "<td>" + (field[j].length == null ? "" : field[j].length) + "</td>"
                            + "<td>" + (field[j].isNullable ? "是" : "否") + "</td>"
                            + "<td>" + (field[j].defaultValue == null ? "" : field[j].defaultValue) + "</td>"
                            + "<td>" + (field[j].columnKey == "PRI" ? "是" : "否") + "</td>"
                            + "<td>" + (field[j].isIdentity ? "是" : "否") + "</td>"
                            + "<td>" + (field[j].description == null ? "" : field[j].description) + "</td>"
                            + "</tr>";
                    }
                    $("#divTableTemp tbody").html(tdHtml);
                    divTableFilesHtml += "<div class='eleDb' db='" + row.dbName + "'>" + $("#divTableTemp").html() + "</div>";

                }
                $("#divDatas").append(divTableListHtml + divTableFilesHtml);

            },
            error: function (result) {
                alert("查询失败");
            }
        });

    });

    $("#pageMSSQL").on("click", '.btnGetData', function () {

        const connectionString = $("#txtConnectionString").val().trim();
        if (!connectionString) {
            return;
        }
        const username = $("#txtUsername").val().trim();
        if (!username) {
            return;
        }
        const password = $("#txtPassword").val().trim();
        if (!password) {
            return;
        }

        const dbType = $("input[name=dbType]:radio[checked=checked]").val();
        const tableName = $(this).parent().attr('db');

        $("#lblActTitle").text(tableName);

        // 初始化查询条件
        initQueryCondition(connectionString, username, password, dbType, tableName);
        $('.divActTitle').fadeIn(200);
        $('#mask').show(0);
        $('#mask').css('width', '100%');
    });

    $("#btnGo").bind("click", function () {
        const connectionString = $("#txtConnectionString").val().trim();
        if (!connectionString) {
            return;
        }
        const username = $("#txtUsername").val().trim();
        if (!username) {
            return;
        }
        const password = $("#txtPassword").val().trim();
        if (!password) {
            return;
        }

        const dbType = $("input[name=dbType]:radio[checked=checked]").val();

        let actType = $("input[name=cboActType]:radio[checked=checked]").val();
        switch (actType) {
            case "s":
                query(connectionString, username, password, dbType, $("#lblActTitle").text());
                break;
            case "e":
                edit(connectionString, username, password, dbType, $("#lblActTitle").text());
                break;
            case "a":
                add(connectionString, username, password, dbType, $("#lblActTitle").text());
                break;
        }

    });

    function query(connectionString, username, password, dbType, tableName) {
        $.post("/db/getDbData", {
            connection: encodeURIComponent(connectionString),
            username: encodeURIComponent(username),
            password: encodeURIComponent(password),
            dbType: dbType,
            tableName: tableName,
            pageNum: $("#txtPageNum").val() ? $("#txtPageNum").val() - 1 : 0,
            pageSize: $("#txtPageSize").val() ? $("#txtPageSize").val() : 10,
            condition: JSON.stringify($("#fmCondition").serializeArray())
        }, function (data) {
            if (data.code != '00000') {
                alert(data.msg);
                return;
            }
            if (data.data.length == 0) {
                alert("无数据");
                return;
            }
            let tBody = '';
            for (let i = 0; i < data.data.length; i++) {
                let tBodyTr = '';
                $("#divCondition").find('.lblCol').each(function () {
                    tBodyTr += "<td>" + (data.data[i][$(this).attr("i")] == null ? "<i class='txtLight'>null</i>" : data.data[i][$(this).attr("i")].toString().replaceAll("<", "&lt;").replaceAll(">", "&gt;")) + "</td>";
                });
                tBody += "<tr>" + tBodyTr + "</tr>";
            }
            $(".divQueryResult tbody").html(tBody);
            $(".divQueryResult").show();
        });
    }

    function edit(connectionString, username, password, dbType, tableName) {
        $.post("/db/editData", {
            connection: encodeURIComponent(connectionString),
            username: encodeURIComponent(username),
            password: encodeURIComponent(password),
            dbType: dbType,
            tableName: tableName,
            condition: JSON.stringify($("#fmCondition").serializeArray())
        }, function (data) {
            if (data.code != '00000') {
                alert(data.msg);
                return;
            }
            alert("修改成功");
            query(connectionString, username, password, dbType, tableName);
        });
    }

    function add(connectionString, username, password, dbType, tableName) {
        $.post("/db/addData", {
            connection: encodeURIComponent(connectionString),
            username: encodeURIComponent(username),
            password: encodeURIComponent(password),
            dbType: dbType,
            tableName: tableName,
            condition: JSON.stringify($("#fmCondition").serializeArray())
        }, function (data) {
            if (data.code != '00000') {
                alert(data.msg);
                return;
            }
            alert("添加成功");
            query(connectionString, username, password, dbType, tableName);
        });
    }

    function initQueryCondition(connectionString, username, password, dbType, tableName) {
        $("#divCondition").html('');
        $(".divQueryResult").hide();
        $(".divQueryResult tbody").html('');
        $.post("/db/getDbFile", {
            connection: encodeURIComponent(connectionString),
            username: encodeURIComponent(username),
            password: encodeURIComponent(password),
            dbType: dbType,
            tableName: tableName
        }, function (data) {
            if (data.data.length == 0) {
                alert("查询表结构异常");
                return;
            }
            let html = '';
            let thead = '';
            for (let i = 0; i < data.data[0].fieldDescriptions.length; i++) {
                let field = data.data[0].fieldDescriptions[i];
                html += '<tr><td><label class=\"lblCol\" i=\"' + field.name + '\">' + field.name + (field.description != null && field.description != '' ? '(' + field.description + ')' : '') + '</label></td>'
                    + '<td><input type=\'text\' name=\'' + field.name + '\' /></td></tr>';
                thead += '<td>' + field.name + '</td>';
            }
            $("#divCondition").html('<form id=\'fmCondition\'><table>' + html + '</table></form>');

            $(".divQueryResult thead").html(thead);
        });

    }

    $("#btnExport").bind("click", function () {
        const connectionString = $("#txtConnectionString").val();
        const sql = $("#txtSql").text();
        const fileName = $("#txtFileName").val();
        if (sql.trim() == '') return;
        $("#fmExport").find("input[name=Connection]").val(encodeURIComponent(connectionString));
        $("#fmExport").find("input[name=fileName]").val(encodeURIComponent(fileName));
        $("#fmExport").find("input[name=DbType]").val($("input[name=dbType]:radio[checked=checked]").val());
        $("#fmExport").find("input[name=excelType]").val($("input[name=excelType]:radio[checked=checked]").val());
        $("#fmExport").find("input[name=sql]").val(encodeURIComponent(sql));
        $("#fmExport").submit();
    });

    $("input:radio").bind("change", function () {
        const sqlserver = "data source=xxxxxxxxxx;database=xxxxxxxxxx;";
        const mysql = "jdbc:mysql://xxxxxxxxxxxx:xxxx/xxxxxxxxxx";
        const postgresql = "jdbc:postgresql://xxxxxxxxxxxx:xxxx/xxx";

        if (this.value == 'SqlServer') {
            if ($("#txtConnectionString").val() == postgresql || $("#txtConnectionString").val() == mysql || $("#txtConnectionString").val() == "") {
                $("#txtConnectionString").val(sqlserver);
            }
        } else if (this.value == 'PostgreSql') {
            if ($("#txtConnectionString").val() == sqlserver || $("#txtConnectionString").val() == mysql || $("#txtConnectionString").val() == "") {
                $("#txtConnectionString").val(postgresql);
            }
        } else if (this.value == 'MySql') {
            if ($("#txtConnectionString").val() == sqlserver || $("#txtConnectionString").val() == postgresql || $("#txtConnectionString").val() == "") {
                $("#txtConnectionString").val(mysql);
            }
        }
    });


</script>

</@page.main>